var express = require('express');
let fs = require("fs");
let path = require("path");
let sprintf = require('sprintf');
var conn = require('../public/javascripts/connect');
let multipart = require('connect-multiparty');
var judge_login = require('../public/javascripts/judge_login');
var parseExcel = require('../public/download/parseExcel');
var department = require('../public/download/department');
var calc = require('../public/Backstage/Calculation');//3级巴的计算
let XLSX = require('xlsx');
var router = express.Router();
var multipartMiddleware = multipart();
let randomstring = require('randomstring');//随机字符串
router.all('*', function (req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "content-type,xfilecategory,xfilename,xfilesize");
    res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By", ' 3.2.1')
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});
let results = {}
/* GET home page. */
router.get('/', async function (req, res, next) {
    let Jurisdiction = null;
    let user = {};
    if (req.cookies.jinshan_rabbing) {
        results.jinshan_rabbing = req.cookies.jinshan_rabbing;
        Jurisdiction = await judge_login.judgelogin(req.cookies.jinshan_rabbing);
        console.log("Jurisdiction登录", Jurisdiction);
        if (Jurisdiction) {
            results.Jurisdiction = JSON.parse(Jurisdiction.department_number);
            results.real_name = Jurisdiction.real_name;
        } else {
            results.Jurisdiction = null;
        }
    } else {
        results.Jurisdiction = null;
    }
    ;
    let deparentNum = req.query.deparentNum;
    //链接基础配置数据库
    console.log("results.Jurisdiction", results.Jurisdiction);
    if (results.Jurisdiction) {
        let sql = 'SELECT * FROM organization ORDER BY parentdepartment_id';
        conn.connection1.query(sql, function (err, result, fields) {
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
                return;
            }
            console.log("dataBar", result);
            results.dataBar = result
            res.render('index', results);
        })
    } else {
        res.render('login', {login: "您还没有登录:请登录"})
    }

});
/*解锁判断是否有解锁权限并且判断是否是3级巴才可以解锁*/
router.post('/unlockauthority', async function (req, res, next) {
    let results = {};
    let Jurisdiction = null;
    let user = {};
    if (req.cookies.jinshan_rabbing) {
        results.jinshan_rabbing = req.cookies.jinshan_rabbing;
        Jurisdiction = await judge_login.judgelogin(req.cookies.jinshan_rabbing);
        if (Jurisdiction) {
            results.Jurisdiction = JSON.parse(Jurisdiction.department_number);
            results.real_name = Jurisdiction.real_name;
        } else {
            results.Jurisdiction = null;
        }
    } else {
        res.render('login', {login: "您还没有登录:请登录"});
    }
    if (!results.Jurisdiction) {
        res.json({resultRes: "当前账号已经过期,请重新登录"});
        return
    }
    let {tableName, grade} = req.body;
    console.log('req.query', req.body);
    console.log('Jurisdiction', Jurisdiction);
    console.log('tablename', tableName);
    console.log('grade', grade);

    if (Jurisdiction.Unlock_authority != 1) {
        res.json({success: `您没有${tableName}的解锁权限`});
        return;
    }
    if (grade != 3) {
        res.json({success: `${grade}级巴部门不能解锁`});
        return;
    }
    let sql = `UPDATE correspondence_table SET state=0 WHERE table_name='${tableName}'`
    conn.connection2.query(sql, function (err, result, fields) {
        if (err) {
            console.log('[UPDATE ERROR] - ', err.message);
            res.json({success: `解锁失败`});
            return;
        }
        res.json({success: `解锁成功`});

    })


})
router.use('/deparentNum', async function (req, res, next) {
    let results = {};
    let Jurisdiction = null;
    let user = {};
    if (req.cookies.jinshan_rabbing) {
        results.jinshan_rabbing = req.cookies.jinshan_rabbing;
        Jurisdiction = await judge_login.judgelogin(req.cookies.jinshan_rabbing);
        console.log("Jurisdiction登录", Jurisdiction);
        if (Jurisdiction) {
            results.Jurisdiction = JSON.parse(Jurisdiction.department_number);
            results.real_name = Jurisdiction.real_name;
            console.log("results.Jurisdiction", results.real_name);
            console.log("=results.jinshan_rabbing", results.jinshan_rabbing);
        } else {
            results.Jurisdiction = null;
        }
    } else {
        res.render('login', {login: "您还没有登录:请登录"});
    }
    if (!results.Jurisdiction) {
        res.json({resultRes: "当前账号已经过期,请重新登录"});
        console.log("你还没有登录")
        return
    }
    let {deparentNum, pageNumber = 1, year, month, state} = req.query;
    console.log("deparentNumpageNumber", req.query)
    if (!year) {
        var time = new Date
        year = time.getFullYear();
    }
    console.log("没有传年份过来的时候", year);
    let sql2, sql1;

    if (!month) {
        if (!state) {
            sql2 = `SELECT COUNT(*) FROM correspondence_table WHERE deparentNum='${deparentNum}' AND year=${year}`;
        } else {
            sql2 = `SELECT COUNT(*) FROM correspondence_table WHERE deparentNum='${deparentNum}' AND year=${year} AND state=${state}`;

        }
    } else {
        if (state) {
            sql2 = `SELECT COUNT(*) FROM correspondence_table WHERE deparentNum='${deparentNum}' AND year=${year} AND month=${month} AND state=${state}`;
        } else {
            sql2 = `SELECT COUNT(*) FROM correspondence_table WHERE deparentNum='${deparentNum}' AND year=${year} AND month=${month}`;
        }
    }
    console.log("sql2", sql2);
    let pageSize = 12;//每页显示多少条数据
    let totalNumber = await new Promise((res) => {
        conn.connection2.query(sql2, function (err, result, fields) {
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
                return;
            }
            console.log("总数", result[0]["COUNT(*)"]);
            res(result[0]["COUNT(*)"] || 0);

        })
    });
    console.log("totalNumber", totalNumber);
    let pageMax = totalNumber / pageSize;//总页数
    if (totalNumber % pageSize == 0) {
    } else {
        pageMax = parseInt(totalNumber / pageSize) + 1;
    }
    new Promise((resP) => {
        //根据筛选条件筛选出来的数据
        if (!month) {
            if (!state) {
                sql1 = `select amiba.correspondence_table.id, amiba.correspondence_table.deparentNum,amiba.correspondence_table.year,amiba.correspondence_table.month,amiba.correspondence_table.year,basicconfiguration.user.real_name ,amiba.correspondence_table.create_time,amiba.correspondence_table.table_name,amiba.correspondence_table.grade ,amiba.correspondence_table.state from amiba.correspondence_table,basicconfiguration.user WHERE amiba.correspondence_table.deparentNum='${deparentNum}' and amiba.correspondence_table.year=${year} and basicconfiguration.user.user_number=amiba.correspondence_table.user_operation  ORDER BY amiba.correspondence_table.year DESC,amiba.correspondence_table.month DESC LIMIT ${(pageNumber - 1) * pageSize},${pageSize}`;//
            } else {
                sql1 = `select amiba.correspondence_table.id, amiba.correspondence_table.deparentNum,amiba.correspondence_table.year,amiba.correspondence_table.month,amiba.correspondence_table.year,basicconfiguration.user.real_name ,amiba.correspondence_table.create_time,amiba.correspondence_table.table_name,amiba.correspondence_table.grade ,amiba.correspondence_table.state from amiba.correspondence_table,basicconfiguration.user WHERE amiba.correspondence_table.deparentNum='${deparentNum}' and amiba.correspondence_table.year=${year} and basicconfiguration.user.user_number=amiba.correspondence_table.user_operation and amiba.correspondence_table.state='${state}' ORDER BY amiba.correspondence_table.year DESC,amiba.correspondence_table.month DESC LIMIT ${(pageNumber - 1) * pageSize},${pageSize}`;//
            }
        } else {
            if (state) {
                sql1 = `select amiba.correspondence_table.id, amiba.correspondence_table.deparentNum,amiba.correspondence_table.year,amiba.correspondence_table.month,amiba.correspondence_table.year,basicconfiguration.user.real_name ,amiba.correspondence_table.create_time,amiba.correspondence_table.table_name,amiba.correspondence_table.grade ,amiba.correspondence_table.state from amiba.correspondence_table,basicconfiguration.user WHERE amiba.correspondence_table.deparentNum='${deparentNum}' and amiba.correspondence_table.year=${year} and basicconfiguration.user.user_number=amiba.correspondence_table.user_operation and amiba.correspondence_table.state='${state}' and amiba.correspondence_table.month='${month}' ORDER BY amiba.correspondence_table.year DESC,amiba.correspondence_table.month DESC LIMIT ${(pageNumber - 1) * pageSize},${pageSize}`;//
            } else {
                sql1 = `select amiba.correspondence_table.id, amiba.correspondence_table.deparentNum,amiba.correspondence_table.year,amiba.correspondence_table.month,amiba.correspondence_table.year,basicconfiguration.user.real_name ,amiba.correspondence_table.create_time,amiba.correspondence_table.table_name,amiba.correspondence_table.grade ,amiba.correspondence_table.state from amiba.correspondence_table,basicconfiguration.user WHERE amiba.correspondence_table.deparentNum='${deparentNum}' and amiba.correspondence_table.year=${year} and basicconfiguration.user.user_number=amiba.correspondence_table.user_operation and amiba.correspondence_table.month='${month}' ORDER BY amiba.correspondence_table.year DESC,amiba.correspondence_table.month DESC LIMIT ${(pageNumber - 1) * pageSize},${pageSize}`;//
            }
        }
        console.log(sql1);
        conn.connection2.query(sql1, function (err, result, fields) {
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
                return;
            }
            // console.log("result", result);
            resP(result)
        })
    }).then(async data => {
        let Modify_authority = 0;
        let sql3 = `select Modify_authority from user where real_name='${results.real_name}' AND sission='${results.jinshan_rabbing}'`;
        await new Promise(resP => {
            conn.connection1.query(sql3, function (err, result, fields) {
                if (err) {
                    console.log('[SELECT ERROR] - ', err.message);
                    return;
                }
                console.log("resultModify_authority", result);
                Modify_authority = result[0].Modify_authority;
                resP(Modify_authority);
            })
        })
        res.json({result: data, Modify_authority: Modify_authority, pageMax: pageMax, pageNumber: pageNumber});
    })
})
//新建功能
router.post("/YeatMonth", async function (req, res, next) {
    let Jurisdiction = null;
    let user = {};
    if (req.cookies.jinshan_rabbing) {
        Jurisdiction = await judge_login.judgelogin(req.cookies.jinshan_rabbing);
        console.log("Jurisdiction登录", Jurisdiction);
        if (Jurisdiction) {
            user.Jurisdiction = JSON.parse(Jurisdiction.department_number);
            user.user_number = Jurisdiction.user_number;
        } else {
            user.Jurisdiction = null;
        }
    } else {
        user.Jurisdiction = null;
    }
    ;
    let arr = JSON.parse(Object.keys(req.body)[0]);
    let table_name1 = `${arr.tableName}_${arr.year}_${arr.month}`;
    let view_name1 = `view_${arr.tableName}_${arr.year}_${arr.month}`;
    let sql = `REPLACE INTO correspondence_table(id,deparentNum,year,month,user_operation,create_time,table_name,grade,state)VALUES(0,${arr.departmentNum},${arr.year},${arr.month},${user.user_number},NOW(),'${table_name1}',${arr.grade},1)`;
    console.log("sqlsqlsql", sql);
    await new Promise(resP => {
        conn.connection2.query(sql, function (err, result, files) {
            if (err) {
                console.log('[INSERT ERROR] - ', err.message);
                res.json({'suc': true});
                return;
            }
            console.log('INSERT ID:', result);
            res.json({'suc': true});
            resP(true)
        })
    })
    //新建一张表begin
    let cretab = `CREATE TABLE IF NOT EXISTS amiba.${table_name1} LIKE basicconfiguration.amibatemplate;`;
    conn.connection2.query(cretab, function (err, result, files) {
        if (err) {
            console.log('[INSERT ERROR] - ', err.message);
            return;
        }
        console.log('INSERT ID:', result);
    })

    //新建一张表对应的视图begin
    let creView = `CREATE VIEW IF NOT EXISTS amiba.${view_name1} AS SELECT * FROM amiba.${table_name1}`;
    await new Promise((res, rej) => {
        conn.connection2.query(creView, function (err, result, files) {
            if (err) {
                console.log('[INSERT ERROR] - ', err.message);
                return;
            }
            console.log('创建视图成功', result);
            res(true)
        })
    })
})
/*汇总前进行判断,3级巴子部门是否已经全部抓取完成并且锁定了,二级巴判断是否已经抓取完成即可*/
router.get("/jusudep", async function (req, res, next) {
    let uploadSummary = req.query.uploadSummary;
    console.log("uploadSummary", uploadSummary);
    // 金石报表系统/阿米巴/2级巴/总部管理费用/2018.11
    let [...arruploadSummary] = uploadSummary.split("/");
    let depName = arruploadSummary[3];//二级巴的部门名称
    let [year, month] = arruploadSummary[4].split(".");//年份月份
    //判断部门是正在抓取,还是抓取异常,或者等待抓取,
    let gsSql = `select 当前状态 from 任务列表 where 部门名称='${depName}_${year}_${month}'`;
    let stateSes = await new Promise(((resP, resJ) => {
        conn.connection4.query(gsSql, function (err, result, files) {
            if (err) {
                console.error("该部门不在任务队列中", err);
                resJ(false);
                return;
            }
            if (result != false) {
                if (result[0]["当前状态"] == "正在抓取" || result[0]["当前状态"] == "等待抓取") {
                    res.json({success: `${depName}_${year}_${month}已经在队列中请勿重复添加`});
                    return;
                }
            }
            resP(result);
        });
    }));

    //下面的子部门
    let sql = `select departmentName from organization where parentdepartment_id=(select deparentNum from organization where departmentName='${depName}')`;
    let result = await new Promise((resP => {
        conn.connection1.query(sql, function (err, result, files) {
            if (err) {
                console.error("查找二级巴子部门报错", err);
                return;
            }
            resP(result);
        })
    }))
    let INarr = [];
    for (let item in result) {
        result[item].departmentName = `'${result[item].departmentName}_${year}_${month}'`;
        INarr.push(result[item].departmentName);
    }
    let INstr = `(${INarr.join(',')})`;
    console.log("INstr", INstr);
    //查找二级巴对应的子部门状态
    let sql1 = `select table_name,state,grade from correspondence_table where table_name IN${INstr}`;
    console.log(sql1);
    let result1 = await new Promise((resP => {
        conn.connection2.query(sql1, function (err, result, files) {
            if (err) {
                console.error("查找某部门报错", err);
                return;
            }
            resP(result);
        })
    }))
    console.log("result1", result1);
    if (result1 == false || (result1.length != result.length)) {//获取的状态数量和子部门数量不等说明有不们还不存在
        res.json({success: '3级巴部门还未创建请创建后汇总'});
        return;
    }
    //判断子部门3级巴状态是否为0(已锁定)锁定全部锁定方可汇总
    for (let item in result1) {
        if (result1[item].grade === 3) {//如果二级巴汇总需要判断3级巴是否锁定
            if (result1[item].state === 2) {
            } else {
                res.json({success: `${result1[item].table_name}还未锁定请锁定确认后汇总`});
                return;
            }
        } else {//如果一级巴汇总需要判断2级巴是否汇总完成
            if (result1[item].state === 0) {
            } else {
                res.json({success: `${result1[item].table_name}还未汇总请汇总后进行1级巴汇总`});
                return;
            }
        }

    }
    //如果可以汇总在任务队列中插入一条记录
    let insertSql = `REPLACE INTO 任务列表 (部门名称,当前状态,创建时间,开始时间) VALUES ('${depName}_${year}_${month}','正在抓取',NOW(),NOW())`;//插入一条记录
    conn.connection4.query(insertSql, function (err, result, files) {
        if (err) {
            console.error("插入汇总状态出错", err);
            return;
        }
    })
    res.json({success: ``});
})
//汇总功能
router.post("/uploadSummary", async function (req, res, next) {
    let Jurisdiction = null;
    let user = {};
    if (req.cookies.jinshan_rabbing) {
        Jurisdiction = await judge_login.judgelogin(req.cookies.jinshan_rabbing);
        console.log("Jurisdiction登录", Jurisdiction);
        if (Jurisdiction) {
            user.Jurisdiction = JSON.parse(Jurisdiction.department_number);
            user.user_number = Jurisdiction.user_number;
        } else {
            user.Jurisdiction = null;
        }
    } else {
        user.Jurisdiction = null;
    }
    ;
    let arr = JSON.parse(Object.keys(req.body)[0]);
    let table_name1 = `${arr.tableName}_${arr.year}_${arr.month}`;
    let sql = `REPLACE INTO correspondence_table(id,deparentNum,year,month,user_operation,create_time,table_name,grade,state)VALUES(0,${arr.departmentNum},${arr.year},${arr.month},${user.user_number},NOW(),'${table_name1}',${arr.grade},1)`;
    let sqlUpdate = `UPDATE correspondence_table SET state=1 WHERE table_name='${table_name1}'`;
    conn.connection2.query(sql, function (err, result, files) {
        if (err) {
            console.log('[INSERT ERROR] - ', err.message);
            res.json({'suc': "重新汇总成功"});
            return;
        }
        console.log('INSERT ID:', result);
        res.json({'success': true});
    })
    //新建一张表begin
    // let cretab=`CREATE TABLE amiba.${table_name1} LIKE basicconfiguration.amibatemplate`;
    let cretab = `CREATE TABLE IF NOT EXISTS amiba.${table_name1} LIKE basicconfiguration.amibatemplate;`;
    conn.connection2.query(cretab, function (err, result, files) {
        if (err) {
            console.log('[INSERT ERROR] - ', err.message);
            return;
        }
        console.log('INSERT ID:', result);
    })

})
//文件上传到服务器并获取调整的值改变数据库
router.post('/upload/xgFilke', multipartMiddleware, async function (req, res) {
    let excelname = req.files.file.originalFilename;
    let tablename = excelname.split(".")[0];
    let patt1 = /^\W+_[0-9]{4}_[0-9]{1,2}\.xlsx$/g;
    if (!patt1.test(excelname)) {
        res.json({success: `请检查您上传的excel表明是否有误:${excelname}`});
        return;
    }
    let sql = `SELECT state FROM correspondence_table WHERE table_name='${tablename}'`;
    let result1 = await new Promise((resP, resJ) => {
        conn.connection2.query(sql, function (err, result, files) {
            if (err) {
                resJ(err);
                return;
            }
            resP(result);
        })
    })
        .then((stateRes) => {
            if (stateRes[0].state == 2) {
                return `${tablename}已经锁定,请解锁后在进行上传`;
            }
            return 1;//为1的时候说明excel的表明和
        })
        .catch(function (error) {
            console.log("error", error);
            return `请检查您上传的excel表明是否有误:${excelname}`;
        })
    if (result1 != 1) {
        res.json({success: result1});
        return;
    }
    let resPath = path.join(__dirname, '../gFilke/out');//上传文件的路径
    let fn = randomstring.generate();
    console.log("tablename", tablename);
    fs.createReadStream(req.files.file.path)
        .pipe(fs.createWriteStream(sprintf(`${resPath}/${req.files.file.originalFilename}`, fn)))
        .on('close', async function () {
            if (tablename.indexOf("其它") != -1 || tablename.indexOf("总部费用") != -1 || tablename.indexOf("经营管理中心") != -1 || tablename.indexOf("物流装备事业部") != -1 || tablename.indexOf("物流发展事业部") != -1 || tablename.indexOf("金石物流") != -1) {
                res.json({success: '上传成功'});
                return;
            }
            //workbook 对象，指的是整份 Excel 文档。我们在使用 js-xlsx 读取 Excel 文档之后就会获得 workbook 对象。
            var workbook = XLSX.readFile(`${resPath}/${req.files.file.originalFilename}`)
            //返回json数据
            const sheetNames = workbook.SheetNames; // 返回 ['sheet1', 'sheet2']
            // 根据表名获取对应某张表
            const worksheet = XLSX.utils.sheet_to_json(workbook.Sheets[sheetNames[2]]);
            console.log("worksheet", worksheet);
            for (let i in worksheet) {//将每一条数据的行数确定
                let keys = Object.getOwnPropertyNames(worksheet[i])[0];
                let num = worksheet[i][keys] + 1;
                worksheet[i].row = num;
            }
            // console.log("worksheet", worksheet);
            let sql;
            for (let i in worksheet) {
                worksheet[i]["F"] = typeof worksheet[i]["F"] == "number" ? worksheet[i]["F"] : 0;
                worksheet[i]["V"] = typeof worksheet[i]["V"] == "number" ? worksheet[i]["V"] : 0;
                worksheet[i]["Y"] = typeof worksheet[i]["Y"] == "number" ? worksheet[i]["Y"] : 0;
                worksheet[i]["AI"] = typeof worksheet[i]["AI"] == "number" ? worksheet[i]["AI"] : 0;
                if (!worksheet[i]["K"]) {
                    sql = `UPDATE ${tablename} SET 去年同期调整=${worksheet[i]["F"]},当月计划值=${worksheet[i]["V"]},当月调整值=${worksheet[i]["Y"]},下月计划值=${worksheet[i]["AI"]} WHERE 行号=${worksheet[i]["row"]}`
                } else {
                    worksheet[i]["K"] = typeof worksheet[i]["K"] == "number" ? worksheet[i]["K"] : 0;
                    sql = `UPDATE ${tablename} SET 去年同期调整=${worksheet[i]["F"]},当月计划值=${worksheet[i]["V"]},当月调整值=${worksheet[i]["Y"]},下月计划值=${worksheet[i]["AI"]},年度计划金额=${worksheet[i]["K"]} WHERE 行号=${worksheet[i]["row"]}`
                }
                // console.log("sql",sql)
                await new Promise((resP, rej) => {
                    conn.connection2.query(sql, function (err, result, fileds) {
                        if (err) {
                            console.log('[UPDATE ERROR]::', err);
                            return;
                        }
                        console.log("i", i, result)
                        resP(true)
                    })
                })
            }
            calc.calculation1.cal(tablename);//执行计算
            res.json({success: '上传成功'});
        });
});
//下载功能
router.post('/download', async function (req, res) {
    console.log(req.body);
    let {tableName, year, month, grade} = req.body;
    let year1, month1, sql, arr, arrFileds;
    let objData = {}, departname = `${tableName}_${year}_${month}`, lineNum = 0,
        premonthdepartname = `${tableName}_${month > 1 ? year : year - 1}_${month > 1 ? month - 1 : 12}`;
    console.log("grade", grade);
    sql = `SELECT A.*,B.去年同期累计 B1,B.当月累计金额 B2 FROM  ${departname} A LEFT JOIN  ${premonthdepartname} B on A.行号=B.行号`;
    arr = ["e", "f", "k", "r", "s", "v", "x", "y", "ai", "i", "m"];
    arrFileds = ["去年同期抓取", "去年同期调整", "年度计划金额", "上月抓取值", "上月调整值", "当月计划值", "当月抓取值", "当月调整值", "下月计划值", "B1", "B2"];

    console.log("sql", sql);
    await new Promise((resP, rej) => {
        conn.connection2.query(sql, function (err, result, fileds) {
            if (err) {
                console.log('[SELECT ERROR]::', err);
                return;
            }
            for (let i in result) {
                lineNum = result[i]["行号"]
                for (let j = 0; j < arrFileds.length; j++) {
                    lineNum1 = arr[j] + lineNum;
                    objData[lineNum1] = result[i][arrFileds[j]]
                }
            }
            // console.log("objData", objData);
            resP(true)
        })
    })
    console.log("department.gongsiName(tableName)", department.gongsiName(tableName));
    let gonsiName = await department.gongsiName(tableName);
    console.log("公司名字", gonsiName);
    // console.log("objData", objData);
    department.renderBiaotou1(objData, gonsiName, year, month, gonsiName[2]).then(async data => {
        await parseExcel({
            src: "/home/HX/www/FirstApp/static/template.xlsx",
            dest: `gFilke/out/${tableName}_${year}_${month}`,
            json: data
        });
        res.json({"dsd": 6556});
    });

});
//删除表格功能
router.post('/deleteform', function (req, res) {
    console.log("req.body", req.body)
    let {tableName, year, month, grade} = req.body;
    //{ tableName: '中化泉州项目部', year: '2018', month: '10', grade: '3' }
    let deletetableName = `${tableName}_${year}_${month}`;
    let sql = ` DELETE FROM correspondence_table WHERE grade=${grade} AND table_name='${deletetableName}'`;
    conn.connection2.query(sql, function (err, result, fileds) {
        if (err) {
            console.log("DELETE ERROR::", err);
            return;
        }
        console.log("result", result)
        res.json({"success": true});
    })
})
//单个抓取队列展示
router.post('/showSingle', async function (req, res) {
    console.log("req.body", req.body);
    let arr = [];
    await new Promise(resP => {
        conn.connection4.query("SELECT * FROM singletask ORDER BY 创建时间", (err, result, fileds) => {
            if (err) {
                console.log(err)
                return;
            }
            console.log("result", result);
            for (let i in result) {
                result[i]["开始时间"] = result[i]["开始时间"] == null ? "0000-00-00 00:00:00" : result[i]["开始时间"].toLocaleString();
                result[i]["结束时间"] = result[i]["结束时间"] == null ? "0000-00-00 00:00:00" : result[i]["结束时间"].toLocaleString();
                if (result[i]["总共用时"] == null) result[i]["总共用时"] = "0";
                arr.push([result[i]["部门名称"], result[i]["业务年份"], result[i]["业务行数"], result[i]["当前状态"], result[i]["创建时间"].toLocaleString(), result[i]["开始时间"], result[i]["结束时间"], result[i]["总共用时"] + "分钟"]);
            }
            console.log("arr", arr);
            resP(true)
        });

    })
    res.json({success: arr})
})
//获取当前用户可以查看操作的部门

//递归遍历所有部门方法封装begin
let permissionallback = async (arg) => {
    /*
    * deparentNum 最上层部门的deparentNum
    * config 遍历后生成一个规则的数组
    *deparentlevelDefault 默认便利到第几层 3 以后如果多余3层可以直接传参即可
    * */
    let {deparentNum, config, deparentlevelDefault = 3} = arg;
    let sql1 = `SELECT * FROM organization WHERE deparentNum='${deparentNum}'`, obj = {};
    console.log("sql1", sql1);
    await new Promise(resP => {
        conn.connection1.query(sql1, async (err, result, fileds) => {
            if (err) {
                console.log(err);
                return;
            }
            console.log("result", result[0]);
            let {id, deparentNum, departmentName, departmentlevel, parentdepartment_id, deparentlevel} = result[0];
            obj.title = departmentName;
            obj.key = deparentNum;
            if (deparentlevel != deparentlevelDefault) {
                obj.children = [];
                let sql2 = `SELECT * FROM organization WHERE parentdepartment_id='${deparentNum}'`;
                console.log("sql2,", sql2);
                await new Promise(resP => {
                    conn.connection1.query(sql2, async (err, result, fileds) => {
                        if (err) {
                            console.log(err)
                            return;
                        }
                        console.log("result2233", result);
                        for (let i = 0; i < result.length; i++) {
                            let {id, deparentNum, departmentName, departmentlevel, parentdepartment_id, deparentlevel} = result[i],
                                obj1 = {}
                            console.log("result[i]", result[i]);
                            console.log("departmentName", departmentName);
                            if (deparentlevel != deparentlevelDefault) {
                                await permissionallback({
                                    deparentNum,
                                    config: obj.children,
                                    deparentlevel: deparentlevelDefault
                                });
                            } else if (deparentlevel == 3) {
                                obj1.title = departmentName;
                                obj1.key = deparentNum;
                                obj.children.push(obj1);
                            }

                        }
                        resP(true)
                    })
                })
            }
            config.push(obj);
            resP(config)
        });
    });

}
//递归遍历所有部门方法end


router.post('/Organization', async function (req, res) {
    console.log("req.body", req.body);
    let {phone, user_pwd} = req.body, config = [],
        sql = `SELECT department_number FROM user WHERE phone='${phone}' AND user_pwd='${user_pwd}'`,
        result0 = await new Promise(resP => {
            conn.connection1.query(sql, (err, result, fileds) => {
                if (err) {
                    console.log(err)
                    return;
                }
                console.log("result", result);
                resP(result)
            });

        });
    //权限判断获取需要展示的侧边栏begin
    let Jurisdiction = JSON.parse(result0[0]["department_number"]);
    if (Jurisdiction.length === 1 && Jurisdiction[0] === 101) {
        await permissionallback({deparentNum: Jurisdiction[0], config, deparentlevel: 3});
        console.log("config", config);
    } else if (Jurisdiction.length === 1 && Jurisdiction[0] != 101) {
        await permissionallback({deparentNum: Jurisdiction[0], config, deparentlevel: 3});
        console.log("config", config);
    } else if (Jurisdiction.length > 1) {//循环判断每个值是否有子部门
        for (let i = 0; i < Jurisdiction.length; i++) {
            console.log("Jurisdiction[i]",Jurisdiction[i]);
            await permissionallback({deparentNum: Jurisdiction[i], config, deparentlevel: 3});
        }
    }
    //权限判断获取需要展示的侧边栏end

    res.json({success: config})
})
module.exports = router;